var db = require('../../config/db')
//查询
exports.search = (req,res)=>{
    // 执行查询
    if(req.query.tar == '1'){
      //用户
      function queryAsync(sql, params) {
        return new Promise((resolve, reject) => {
          db.query(sql, params, (err, results) => {
            if (err) {
              reject(err);
            } else {
              resolve(results);
            }
          });
        });
      }
      
      db.query('SELECT * FROM land_resource WHERE userid = ?', req.query.id, async (err, results) => {
        try {
          if (err) throw err;
          if (results.length < 1) {
            return res.send({ status: 404, message: '无记录' });
          }
      
          let finalResults = [];
      
          for (let i = 0; i < results.length; i++) {
            const item = results[i];
            const landParcelResults = await queryAsync('SELECT * FROM land_parcel WHERE landresourceid=?', item.id);
      
            if (landParcelResults.length < 1) {
              return res.send({ status: 404, message: '无记录' });
            }
      
            for (let j = 0; j < landParcelResults.length; j++) {
              const item1 = landParcelResults[j];
              const vegetablePlotResults = await queryAsync('SELECT * FROM vegetable_plot WHERE landparcelid=?', item1.id);
      
              vegetablePlotResults.forEach((item2) => {
                item2.starttime.setDate(item2.starttime.getDate() + 1);
                item2.starttime = new Date(item2.starttime).toISOString().split('T')[0];
                item2.endtime.setDate(item2.endtime.getDate() + 1);
                item2.endtime = new Date(item2.endtime).toISOString().split('T')[0];
                item2.order = item1.order;
                item2.area = item1.area;
                item2.ph = item1.ph;
                item2.address = item.address;
                item2.landresourcename = item.name;
              });
      
              finalResults.push(...vegetablePlotResults);
            }
          }
      
          res.send({
            status: 200,
            message: '查询成功',
            data: finalResults,
          });
        } catch (error) {
          res.cc('500', error.message);
        }
      });
    }else{
      const query = `
    SELECT lp.*
    FROM land_parcel lp
    JOIN vegetable_plot vp ON lp.id = vp.landparcelid
    WHERE vp.landparcelid = ?
`;
const getLandParcelInfo = (landparcelid) => {
  return new Promise((resolve, reject) => {
    db.query(query, landparcelid, (err, results) => {
      if (err) {
        reject(err);
      } else {
        resolve(results);
      }
    });
  });
};
    const query1 =`SELECT ui.*
    FROM land_parcel lp
    JOIN land_resource lr ON lp.landresourceid = lr.id
    JOIN user_info ui ON lr.userid = ui.id
    WHERE lp.landresourceid = ?
    `
const getUserInfo = (landresourceid) => {
    return new Promise((resolve, reject) => {
      db.query(query1, landresourceid, (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  };
  const query2 =`SELECT lr.*
    FROM land_parcel lp
    JOIN land_resource lr ON lp.landresourceid = lr.id
    WHERE lp.landresourceid = ?
    `
  const getAddressInfo = (landresourceid) => {
    return new Promise((resolve, reject) => {
      db.query(query2, landresourceid, (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  };
const processData = async () => {
  try {
    const results = await new Promise((resolve, reject) => {
      db.query('select * from vegetable_plot', (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
    if (results.length < 1) {
      return res.cc('404', '记录为空');
    }
    for (let i = 0; i < results.length; i++) {
      results[i].starttime.setDate(results[i].starttime.getDate() + 1);
      results[i].starttime = new Date(results[i].starttime).toISOString().split('T')[0];
      results[i].endtime.setDate(results[i].endtime.getDate() + 1);
      results[i].endtime = new Date(results[i].endtime).toISOString().split('T')[0];
      const landParcelInfo = await getLandParcelInfo(results[i].landparcelid);
      results[i].order = landParcelInfo[0].order;
      results[i].area = landParcelInfo[0].area;
      results[i].ph = landParcelInfo[0].ph;
      const addressInfo = await getAddressInfo(landParcelInfo[0].landresourceid);//拿位置
      results[i].address = addressInfo[0].address;
      results[i].landresourcename = addressInfo[0].name;
      const userInfo = await getUserInfo(landParcelInfo[0].landresourceid);
      results[i].username = userInfo[0].name;
      results[i].phone = userInfo[0].phone;
    }

    res.send({
      status: 200,
      message: '查询成功',
      data: results,
    });
  } catch (error) {
    res.cc('500', error);
  }
};
processData();

    }
}
//添加
exports.add = (req,res)=>{
    var vagetablePlotMes = req.body
    if(vagetablePlotMes.tar =='1'){
      //用户
      db.query('SELECT * FROM vegetable_plot WHERE name = ?', vagetablePlotMes.name, (err, results)=>{
        if(err) return res.cc('404', err.message)
        if(results.length< 1) {//名不冲突的情况
          db.query('SELECT * FROM land_resource WHERE name = ? and userid=?', [vagetablePlotMes.landresourcename,vagetablePlotMes.userid], (err, results1)=>{
            if(err) return res.cc('404', err.message)
            if(results1.length == 1){//土地资源存在,紧接着判断地号
              //可以拿result1的id，去关联land_parcel记录是否存在 order == order（前端填写的表格）
            db.query('SELECT * FROM land_parcel WHERE landresourceid = ?', results1[0].id, (err, results2) => {
              var i
              for (i = 0; i < results2.length; i++) {
                if (results2[i].order == vagetablePlotMes.order) {//存在记录的地块信息
                    if (results2[i].status === '在植') {
                        return res.send({ status: 404, message: '添加失败：该土地块编号正在种植其他蔬菜' });
                    }else{
                      vagetablePlotMes.landparcelid = results2[i].id;
                    vagetablePlotMes.status = '否';
                    const { order, landresourcename, id,oldendtime,tar,userid, ...rest } = vagetablePlotMes;
                    // 开启事务
                    db.beginTransaction(function(err) {
                      if (err) throw err;
                      db.query('INSERT INTO vegetable_plot SET ?', rest, (err, results3) => {
                          // 第一个操作：插入蔬菜种植信息
                          if (err) {
                              return db.rollback(function() {
                                  res.send({ status: 404, message: err.message });
                              });
                          }
                          if (results3.affectedRows !== 1) {
                              return db.rollback(function() {
                                  res.send({ status: 404, message: '添加失败，请稍后再试' });
                              });
                          }
                          // 第二个操作：更新土地状态
                          db.query('UPDATE land_parcel SET status=? WHERE id=?', ['在植', vagetablePlotMes.landparcelid], (err, results4) => {
                              if (err) {
                                  return db.rollback(function() {
                                      res.send({ status: 404, message: err.message });
                                  });
                              }
                              if (results4.affectedRows !== 1) {
                                  return db.rollback(function() {
                                      res.send({ status: 404, message: '更新失败，请稍后再试' });
                                  });
                              }
                              // 提交事务并发送成功消息
                              db.commit(function(err) {
                                  if (err) {
                                      return db.rollback(function() {
                                          res.send({ status: 404, message: '提交事务失败' });
                                      });
                                  }
                                  res.send({ status: 200, message: '添加成功' });
                              });
                          });
                      });
                    });
                    break;
                    }
                }
              }if(i == results2.length) res.send({ status: 404, message: '添加失败：输入的土地块编号不存在' });
              
          });
            }
            else{
              res.send({status: 404, message: '添加失败：该土地资源名称不属于您'})
            }
          })
        }
        else{
          res.send({status: 404, message: '添加失败：菜地名称已存在'})}
      })
    }else{//管理员
      db.query('SELECT * FROM vegetable_plot WHERE name = ?', vagetablePlotMes.name, (err, results)=>{
        if(err) return res.cc('404', err.message)
        if(results.length< 1) {//名不冲突的情况
          db.query('SELECT * FROM land_resource WHERE name = ?', vagetablePlotMes.landresourcename, (err, results1)=>{
            if(err) return res.cc('404', err.message)
            if(results1.length == 1){//土地资源存在,紧接着判断地号
              //可以拿result1的id，去关联land_parcel记录是否存在 order == order（前端填写的表格）
            db.query('SELECT * FROM land_parcel WHERE landresourceid = ?', results1[0].id, (err, results2) => {
              for (let i = 0; i < results2.length; i++) {
                if (results2[i].order == vagetablePlotMes.order) {
                    if (results2[i].status === '在植') {
                        return res.send({ status: 404, message: '添加失败：该土地块编号正在种植其他蔬菜' });
                    }
                    vagetablePlotMes.landparcelid = results2[i].id;
                    vagetablePlotMes.status = '否';
                    const { order, landresourcename, id,oldendtime, ...rest } = vagetablePlotMes;
                    // 开启事务
                    db.beginTransaction(function(err) {
                      if (err) throw err;
                      db.query('INSERT INTO vegetable_plot SET ?', rest, (err, results3) => {
                          // 第一个操作：插入蔬菜种植信息
                          if (err) {
                              return db.rollback(function() {
                                  res.send({ status: 404, message: err.message });
                              });
                          }
                          if (results3.affectedRows !== 1) {
                              return db.rollback(function() {
                                  res.send({ status: 404, message: '添加失败，请稍后再试' });
                              });
                          }
                          // 第二个操作：更新土地状态
                          db.query('UPDATE land_parcel SET status=? WHERE id=?', ['在植', results2[0].id], (err, results4) => {
                              if (err) {
                                  return db.rollback(function() {
                                      res.send({ status: 404, message: err.message });
                                  });
                              }
                              if (results4.affectedRows !== 1) {
                                  return db.rollback(function() {
                                      res.send({ status: 404, message: '更新失败，请稍后再试' });
                                  });
                              }
                              // 提交事务并发送成功消息
                              db.commit(function(err) {
                                  if (err) {
                                      return db.rollback(function() {
                                          res.send({ status: 404, message: '提交事务失败' });
                                      });
                                  }
                                  res.send({ status: 200, message: '添加成功' });
                              });
                          });
                      });
                    });
                    break;
                }
                else{
                  res.send({ status: 404, message: '添加失败：输入的土地块编号不存在' });
                }
              }
          });
  
            }
            else{
              res.send({status: 404, message: '添加失败：输入的土地资源名称不存在'})
            }
          })
        }
        else{
          res.send({status: 404, message: '添加失败：菜地名称已存在'})}
      })

    }
    
}
//删除
exports.delete = (req,res)=>{
  db.query(`select * from planting_output where vegetableid =?`, req.query.id,(err, results)=>{
    if(err) return res.cc('500', err)
    if(results.length == 1){
      res.send({status: 500, message: '删除失败，存在关联的菜地产量信息，请先删除'})
    }
    else{
      if(req.query.status == '是'){
        db.query('DELETE FROM vegetable_plot WHERE id = ?', req.query.id, (err, results2)=>{
          if(err) return res.cc('404', err.message)
          if(results2.affectedRows !== 1) return res.cc('500', '删除失败');
          res.send({status: 200, message: '删除成功'})
        })
      }else{
        db.query('update land_parcel set status=? where id=?', ['空闲' ,req.query.landparcelid], (err, results3) => {
          //
          db.query('DELETE FROM vegetable_plot WHERE id = ?', req.query.id, (err, results4)=>{
            if(err) return res.cc('404', err.message)
            if(results4.affectedRows !== 1) return res.cc('500', '删除失败');
            res.send({status: 200, message: '删除成功'})
          })

        })
      }
    }
  })
  
}
//更新
exports.up = (req, res)=>{
  var vagetablePlotMes = req.body
  if(vagetablePlotMes.tar = '1'){
    //用户
  const query = 'SELECT * FROM vegetable_plot WHERE name = ? AND id = ?'
  const params = [vagetablePlotMes.name, vagetablePlotMes.id];
    db.query(query, params, (err, results)=>{
      if(err) return res.cc('404', err.message)
      if(results.length ==  1){//没改名
        const today = new Date();
        const todayDate = `${today.getFullYear()}-${(today.getMonth() + 1).toString().padStart(2, '0')}-${today.getDate().toString().padStart(2, '0')}`;
        if(todayDate <req.body.oldendtime){//原来的结束时间，种植期间可以改
          if(todayDate<req.body.endtime){//修改的时间判断状态
            db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '否', req.body.id], (err, up_ve_results)=>{
              if(err) return res.cc('404', err.message)
              if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
              db.query('update land_parcel  set status=? WHERE id=?', ['在植', req.body.landparcelid], (err, up_parcel_results)=>{
                if(err) return res.cc('404', err.message)
                if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                res.send({status: 200, message: '修改成功'})
              })
            })
          }
          else{
            db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '是', req.body.id], (err, up_ve_results)=>{
              if(err) return res.cc('404', err.message)
              if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
              db.query('update land_parcel  set status=? WHERE id=?', ['空闲', req.body.landparcelid], (err, up_parcel_results)=>{
                if(err) return res.cc('404', err.message)
                if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                res.send({status: 200, message: '修改成功'})
              })
            })
          }
        }
        else{
          res.send({status: 400, message: '修改失败：该菜地种植已经完成不能修改时间'})
        }
      }
      else{//改名的开发
        //判断重名
        db.query('SELECT * FROM vegetable_plot WHERE name = ?', vagetablePlotMes.name, (err, vege_name_results)=>{
          //
          if(vege_name_results.length == 1){
            res.send({status: 400, message: '修改失败：菜地名存在冲突'})
          }
          else{//插入
            const today = new Date();
            const todayDate = `${today.getFullYear()}-${(today.getMonth() + 1).toString().padStart(2, '0')}-${today.getDate().toString().padStart(2, '0')}`;
            if(todayDate <req.body.oldendtime){//原来的结束时间，种植期间可以改
              if(todayDate<req.body.endtime){
                db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '否', req.body.id], (err, up_ve_results)=>{
                  if(err) return res.cc('404', err.message)
                  if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                  db.query('update land_parcel  set status=? WHERE id=?', ['在植', req.body.landparcelid], (err, up_parcel_results)=>{
                    if(err) return res.cc('404', err.message)
                    if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                    res.send({status: 200, message: '修改成功'})
                  })
                })
              }
              else{
                db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '是', req.body.id], (err, up_ve_results)=>{
                  if(err) return res.cc('404', err.message)
                  if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                  db.query('update land_parcel  set status=? WHERE id=?', ['空闲', req.body.landparcelid], (err, up_parcel_results)=>{
                    if(err) return res.cc('404', err.message)
                    if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                    res.send({status: 200, message: '修改成功'})
                  })
                })
              }
            }
            else{
              res.send({status: 400, message: '修改失败：该菜地种植已经完成不能修改时间'})
            }
          }
        })

      }
  })
  }else{
  const query = 'SELECT * FROM vegetable_plot WHERE name = ? AND id = ?'
  const params = [vagetablePlotMes.name, vagetablePlotMes.id];
    db.query(query, params, (err, results)=>{
      if(err) return res.cc('404', err.message)
      if(results.length ==  1){//没改名
        const today = new Date();
        const todayDate = `${today.getFullYear()}-${(today.getMonth() + 1).toString().padStart(2, '0')}-${today.getDate().toString().padStart(2, '0')}`;
        if(todayDate <req.body.oldendtime){//原来的结束时间，种植期间可以改
          if(todayDate<req.body.endtime){
            db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '否', req.body.id], (err, up_ve_results)=>{
              if(err) return res.cc('404', err.message)
              if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
              db.query('update land_parcel  set status=? WHERE id=?', ['在植', req.body.landparcelid], (err, up_parcel_results)=>{
                if(err) return res.cc('404', err.message)
                if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                res.send({status: 200, message: '修改成功'})
              })
            })
          }
          else{
            db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '是', req.body.id], (err, up_ve_results)=>{
              if(err) return res.cc('404', err.message)
              if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
              db.query('update land_parcel  set status=? WHERE id=?', ['空闲', req.body.landparcelid], (err, up_parcel_results)=>{
                if(err) return res.cc('404', err.message)
                if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                res.send({status: 200, message: '修改成功'})
              })
            })
          }
        }
        else{
          res.send({status: 400, message: '修改失败：该菜地种植已经完成不能修改时间'})
        }
      }
      else{//改名的开发
        //判断重名
        db.query('SELECT * FROM vegetable_plot WHERE name = ?', vagetablePlotMes.name, (err, vege_name_results)=>{
          //
          if(vege_name_results.length == 1){
            res.send({status: 400, message: '修改失败：菜地名存在冲突'})
          }
          else{//插入
            const today = new Date();
            const todayDate = `${today.getFullYear()}-${(today.getMonth() + 1).toString().padStart(2, '0')}-${today.getDate().toString().padStart(2, '0')}`;
            if(todayDate <req.body.oldendtime){//原来的结束时间，种植期间可以改
              if(todayDate<req.body.endtime){
                db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '否', req.body.id], (err, up_ve_results)=>{
                  if(err) return res.cc('404', err.message)
                  if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                  db.query('update land_parcel  set status=? WHERE id=?', ['在植', req.body.landparcelid], (err, up_parcel_results)=>{
                    if(err) return res.cc('404', err.message)
                    if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                    res.send({status: 200, message: '修改成功'})
                  })
                })
              }
              else{
                db.query('update vegetable_plot  set name=?, crops=?, fertilizationcycle=?, fertilizer=?, starttime=?,endtime=?, status=? WHERE id=?', [req.body.name, req.body.crops, req.body.fertilizationcycle, req.body.fertilizer,req.body.starttime, req.body.endtime, '是', req.body.id], (err, up_ve_results)=>{
                  if(err) return res.cc('404', err.message)
                  if(up_ve_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                  db.query('update land_parcel  set status=? WHERE id=?', ['空闲', req.body.landparcelid], (err, up_parcel_results)=>{
                    if(err) return res.cc('404', err.message)
                    if(up_parcel_results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                    res.send({status: 200, message: '修改成功'})
                  })
                })
              }
            }
            else{
              res.send({status: 400, message: '修改失败：该菜地种植已经完成不能修改时间'})
            }
          }
        })

      }
  })

  }
}